# _*_ coding:utf-8 _*_
# @File  : price_position.py
# @Time  : 2021-11-19  15:22
# @Author: zizle

import datetime
import numpy as np
import pandas as pd
from fastapi import APIRouter, Depends, Query, Path
from interfaces.depends import require_start_date, require_end_date
from category import VARIETY_NAME
from status import r_status
from db import FAConnection

pp_dsas_api = APIRouter()


def format_number(x, point_count=2):
    if pd.isna(x):
        return '-'
    else:
        return int(x) if int(x) == float(x) else round(x, point_count)


# 指数和权重指数波动率接口
@pp_dsas_api.get('/vix/', summary='指数波动率')
async def price_vix(ds: datetime.datetime = Depends(require_start_date),
                    de: datetime.datetime = Depends(require_end_date)):

    # 查询数据
    query_sql = 'SELECT quotes_ts,variety_en,contract,close_price,position_price,position_volume,' \
                'long_position,short_position ' \
                'FROM dat_futures_price_position ' \
                'WHERE quotes_ts>=%s AND quotes_ts<=%s AND variety_en=contract;'
    db = FAConnection(conn_name='波动率数据查询')
    records = db.query(query_sql, [int(ds.timestamp()), int(de.timestamp())])
    df = pd.DataFrame(records)

    # 去收盘价和持仓总价为0的数据
    df = df[(df['close_price'] > 0) & (df['position_price'] > 0)]

    if df.empty:
        return {'code': r_status.SUCCESS, 'message': '获取品种波动数据成功!', 'data': []}
    df['pp_date'] = df['quotes_ts'].apply(lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))

    # 计算权重指数
    df['weight_price'] = df['position_price'] / df['position_volume']
    # 以品种分组，计算最高、最低、平均
    ret_df = df.groupby(by=['variety_en'], as_index=False)[['close_price', 'weight_price']].agg(['max', 'min', 'mean'])
    ret_df.columns = [f'{i}_{j}' for i, j in ret_df.columns]
    # 将每一行的index加到列
    ret_df.reset_index(inplace=True)
    # 并入最新日期和收盘价
    latest_close = df[df['quotes_ts'] == df['quotes_ts'].max()]
    ret_df = pd.merge(ret_df, latest_close[['pp_date', 'variety_en', 'close_price', 'weight_price']], on=['variety_en'])
    # 计算波动率
    ret_df['close_price_vix'] = ret_df['close_price_mean'] / ret_df['close_price']
    ret_df['weight_price_vix'] = ret_df['weight_price_mean'] / ret_df['weight_price']
    ret_df.replace(np.inf, np.nan, inplace=True)
    # 数据小数格式
    response_data = ret_df.to_dict(orient='records')
    for item in response_data:
        item['variety_name'] = VARIETY_NAME.get(item['variety_en'], item['variety_en'])
        item['close_price_max'] = format_number(item['close_price_max'])
        item['close_price_min'] = format_number(item['close_price_min'])
        item['close_price_mean'] = format_number(item['close_price_mean'])
        item['close_price'] = format_number(item['close_price'])
        item['weight_price_max'] = format_number(item['weight_price_max'])
        item['weight_price_min'] = format_number(item['weight_price_min'])
        item['weight_price_mean'] = format_number(item['weight_price_mean'])
        item['weight_price'] = format_number(item['weight_price'])
        item['close_price_vix'] = format_number(item['close_price_vix'])
        item['weight_price_vix'] = format_number(item['weight_price_vix'])

    return {'code': r_status.SUCCESS, 'message': '获取价格波动数据成功!', 'data': response_data}


# 品种指数和权重指数波动率接口
@pp_dsas_api.get('/vix/{variety_en}/', summary='指定品种的波动率')
async def variety_price_vix(ds: datetime.datetime = Depends(require_start_date),
                            de: datetime.datetime = Depends(require_end_date),
                            variety_en: str = Path(..., min_length=1, max_length=2)):
    # 查询数据
    query_sql = 'SELECT quotes_ts,variety_en,close_price,position_price,position_volume ' \
                'FROM dat_futures_price_position ' \
                'WHERE quotes_ts>=%s AND quotes_ts<=%s AND variety_en=contract AND variety_en=%s;'
    db = FAConnection(conn_name='品种波动率数据查询')
    records = db.query(query_sql, [int(ds.timestamp()), int(de.timestamp()), variety_en])
    df = pd.DataFrame(records)

    # 去收盘价和持仓总价为0的数据
    df = df[(df['close_price'] > 0) & (df['position_price'] > 0)]

    if df.empty:
        return {'code': r_status.SUCCESS, 'message': '获取品种价格波动数据成功!', 'data': []}
    # 处理数据
    df.sort_values(by=['quotes_ts'], inplace=True)
    df['weight_price'] = df['position_price'] / df['position_volume']
    df['pp_date'] = df['quotes_ts'].apply(lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))

    # 做出一列行号
    df['count'] = [i for i in range(1, df.shape[0] + 1)]

    del df['position_price']
    del df['position_volume']

    # df.reset_index(inplace=True)  # 会出现一个index为名的列值=原index
    # 收盘价相关数据
    df['cp_cum_mean'] = df['close_price'].cumsum() / (df['count'])  # 收盘价滚动均值
    df['cp_cum_max'] = df['close_price'].cummax()  # 收盘价滚动最大值
    df['cp_cum_min'] = df['close_price'].cummin()  # 后盘价滚动最小值
    df['cp_vix'] = df['cp_cum_mean'] / df['close_price']  # 每日波动率

    # 权重指数相关数据
    df['wp_cum_mean'] = df['weight_price'].cumsum() / (df['count'])
    df['wp_cum_max'] = df['weight_price'].cummax()
    df['wp_cum_min'] = df['weight_price'].cummin()
    df['wp_vix'] = df['wp_cum_mean'] / df['weight_price']

    df.replace(np.inf, np.nan, inplace=True)
    # 数据小数格式
    response_data = df.to_dict(orient='records')
    for item in response_data:
        item['variety_name'] = VARIETY_NAME.get(item['variety_en'], item['variety_en'])

        item['close_price'] = format_number(item['close_price'])
        item['cp_cum_mean'] = format_number(item['cp_cum_mean'])
        item['cp_cum_max'] = format_number(item['cp_cum_max'])
        item['cp_cum_min'] = format_number(item['cp_cum_min'])
        item['cp_vix'] = format_number(item['cp_vix'], 4)

        item['weight_price'] = format_number(item['weight_price'])
        item['wp_cum_mean'] = format_number(item['wp_cum_mean'])
        item['wp_cum_max'] = format_number(item['wp_cum_max'])
        item['wp_cum_min'] = format_number(item['wp_cum_min'])
        item['wp_vix'] = format_number(item['wp_vix'], 4)
    return {'code': r_status.SUCCESS, 'message': '获取品种价格波动数据成功!', 'data': response_data}

